from openpyxl import load_workbook

class ExcelUtils:
    def __init__(self,file,sheet_name):
        self.file = file
        self.wb = load_workbook(file)
        self.sheet = self.wb[sheet_name]

    def read(self):
        one = [i.value for i in self.sheet[1]]
        data = []
        for row in self.sheet.iter_rows(min_row=2,values_only=True):
            data.append(dict(zip(one,row)))
        return data

    def write(self,row,col,value):
        self.sheet.cell(row=row, column=col, value=value)
        self.wb.save(self.file)

    def close(self):
        self.wb.close()

    def write_name(self,row_name,col_name,value):
        row_headers = []
        row_idx = 1  # 行表头从第1行开始
        while True:
            cell_value = self.sheet.cell(row=row_idx, column=1).value  # 第1列的单元格值
            if cell_value is None:  # 遇到空单元格，停止读取行表头
                break
            row_headers.append(cell_value)
            row_idx += 1

        col_headers = []
        col_idx = 1  # 列表头从第1列开始
        while True:
            cell_value = self.sheet.cell(row=1, column=col_idx).value  # 第1行的单元格值
            if cell_value is None:  # 遇到空单元格，停止读取列表头
                break
            col_headers.append(cell_value)
            col_idx += 1

        target_row = None
        for idx, header in enumerate(row_headers, start=1):  # 行号从1开始
            if header == row_name:
                target_row = idx
                break

        target_col = None
        for idx, header in enumerate(col_headers, start=1):  # 列号从1开始
            if header == col_name:
                target_col = idx
                break

        target_cell = self.sheet.cell(row=target_row, column=target_col, value=value)
        self.wb.save("chanshu.xlsx")

